package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

/**
 * Performs email dao operations
 * @author Sukhjinder
 */
class EmailSqlDao implements EmailDao {
	
	// Connection
	private Connection conn;
	
	// Statements
    private final String INSERT = "INSERT INTO emails VALUES(?, ?, ?, ?, ?, ?, ?);";
    private final String SELECT = "SELECT * FROM emails WHERE Id = ?;";
    private final String SELECTMAILS = "SELECT * FROM emails WHERE FolderId = ?;";
    private final String UPDATE = "UPDATE emails " +
    		"SET ToEmail = ?, " +
    		" FromEmail = ?, " +
    		" Subject = ?, " +
    		" Body = ?, " +
    		" Read = ?, " +
    		" Ts = ?, " +
    		" FolderId = ? " +
    		"WHERE Id = ?;";
    private final String UPDATEFOLDER = "UPDATE emails " +
    		"SET FolderId = ? " +
    		"WHERE Id = ?;";
    private final String DELETE = "DELETE FROM emails WHERE Id = ?;";
    
    /**
     * Constructor
     * @param conn connection object for database
     */
    public EmailSqlDao(Connection conn) {
		this.conn = conn;
	}

	@Override
    public int insert(Map<String, String> properties) throws Exception {
        PreparedStatement statement = conn.prepareStatement(INSERT); 
        conn.setAutoCommit(true);
        int emailId=-1;
        
        statement.setString(1, properties.get("to"));
        statement.setString(2, properties.get("from"));
        statement.setString(3, properties.get("subject"));
        statement.setString(4, properties.get("body"));
        statement.setString(5, properties.get("read"));
        statement.setString(6, properties.get("timestamp"));
        statement.setString(7, properties.get("folderId"));
        statement.executeUpdate();
        
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()){
        	emailId=rs.getInt(1);
        }
        rs.close();
        
        return emailId;                
    }

    @Override
    public Map<String, String> select(int id) throws Exception {
        Map<String, String> properties = new HashMap<>();

        PreparedStatement statement = conn.prepareStatement(SELECT);
        statement.setString(1, String.valueOf(id));
        ResultSet rs = statement.executeQuery();
        rs.next();
        properties.put("id", rs.getString(1));
        properties.put("to", rs.getString(2));
        properties.put("from", rs.getString(3));
        properties.put("subject", rs.getString(4));
        properties.put("body", rs.getString(5));
        properties.put("read", rs.getString(6));
        properties.put("timestamp", rs.getString(7));
        properties.put("folderId", rs.getString(8));
        rs.close();
            
        return properties;
    }
    
    @Override
	public Map<Integer, String[]> selectMails(int folderId) throws Exception {
		
		Map<Integer, String[]> properties = new HashMap<>();
		String folderProperties[] = new String[7];
		int index = 0, key;
        PreparedStatement statement = conn.prepareStatement(SELECTMAILS);
        statement.setString(1, String.valueOf(folderId));
        ResultSet rs = statement.executeQuery();
        if (rs.next() == false) {
        	folderProperties[index] = new String("No Records found");
        	properties.put(index, folderProperties);
        } else {
        	do
            {
        		index = 0;
        		key = rs.getInt(1);
        		folderProperties[index++] = rs.getString(2);
        		folderProperties[index++] = rs.getString(3);
        		folderProperties[index++] = rs.getString(4);
        		folderProperties[index++] = rs.getString(5);
        		folderProperties[index++] = rs.getString(6);
        		folderProperties[index++] = rs.getString(7);
        		folderProperties[index++] = rs.getString(8);
        		properties.put(key, folderProperties);
        	} while (rs.next());                    
        }
        rs.close();            
        return properties;
	}

    @Override
    public void update(Map<String, String> properties) throws Exception {
        PreparedStatement statement = conn.prepareStatement(UPDATE);
        conn.setAutoCommit(true);
        statement.setString(1, properties.get("to"));
        statement.setString(2, properties.get("from"));
        statement.setString(3, properties.get("subject"));
        statement.setString(4, properties.get("body"));
        statement.setString(5, properties.get("read"));
        statement.setString(6, properties.get("timestamp"));
        statement.setString(7, properties.get("folderId"));
        statement.setString(8, properties.get("id"));
        statement.executeUpdate();
    }

	@Override
	public void updateFolder(Map<String, String> properties) throws Exception {
		PreparedStatement statement = conn.prepareStatement(UPDATEFOLDER);
        conn.setAutoCommit(true);
        statement.setString(1, properties.get("folderId"));
        statement.setString(2, properties.get("id")); 
        statement.executeUpdate();
	}

	@Override
	public void delete(int id) throws Exception {
		PreparedStatement statement = conn.prepareStatement(DELETE);
        conn.setAutoCommit(true);
        statement.setString(1, String.valueOf(id));
        statement.executeUpdate();
	}
}
